﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using BE;
using BC;
using System.Configuration;
using Microsoft.ApplicationBlocks.Data;
using COMExcel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Threading;
using System.Data.SqlClient;

namespace ModuleDuoc
{
    public partial class frmTK_NhapHangcs : ModuleDuoc.Base.frmTK_Base
    {
        public frmTK_NhapHangcs()
        {
            InitializeComponent();
        }

        /// <summary>
        /// Lấy dữ liệu
        /// </summary>
        private void getData(string sophieu)
        {
            string sql = "SELECT * FROM view_THUOC_PHIEU_NHAPKHO ";
            if (cboLoaiPhieu.SelectedValue.ToString() != "")
            {
                sql += " where loaiphieu='" + cboLoaiPhieu.SelectedValue.ToString() + "'" + sophieu ;
            }
            if (radHuy.Checked)
            {
                sql += " and huy=1";
            }
            else if (radHuy.Checked == false)
            {
                sql += " and huy=0";
            }
            sql +=" order by id desc";

            DataTable dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql).Tables[0];

            //if (dt.Rows.Count > 0)
            //{
                this.dgvdata.DataSource = dt;
                this.lblTongPhieu.Text = dt.Rows.Count.ToString() + " phiếu";
                double tongtien = 0;
                for (int i = 0; i < dgvdata.Rows.Count; i++)
                {
                    if (bool.Parse(dgvdata.Rows[i].Cells["huy"].Value.ToString()) == true)
                    {
                        dgvdata.Rows[i].DefaultCellStyle.BackColor = Color.Maroon;
                        tongtien += double.Parse(dgvdata.Rows[i].Cells["tongtien"].Value.ToString());
                    }
                }
                this.lblTongTien.Text = string.Format("{0,0:0}", tongtien);
                this.lblThanhTien.Text = DICHSO.XL_DOC_SO.DOC_SO_CHUOI(tongtien, "đồng", "", 0);
            //}
        }
        private void loadNCC()
        {
            string sql = "SELECT TENNCC,MANCC from zD_NHACUNGCAP union select '',''";
            DataTable dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql).Tables[0];
            this.cboNhaCC.DataSource = dt;
            this.cboNhaCC.DisplayMember = "TENNCC";
            this.cboNhaCC.ValueMember = "MANCC";
        }

        private string getDetailsName()
        {
            string sql_u = "SELECT * FROM view_THUOC_PHIEUNHAPKHOCT where ngaylap between '" + dtpFrom.Value.ToString("MM/dd/yyyy HH:mm:ss") + "' and '" + dtpTo.Value.ToString("MM/dd/yyyy HH:mm:ss") + "'";
            if (cboTenThuoc.SelectedValue.ToString() != "")
            {
                sql_u += " and mavt='" + cboTenThuoc.SelectedValue.ToString() + "'";
            }
            if (txtSoLo.Text.Trim().Length > 0)
            {
                sql_u += " and solo='" + this.txtSoLo.Text.Trim()+ "'";
            }
            if (cboNhaCC.SelectedValue.ToString() != "")
            {
                sql_u += " and nhacc='" + cboNhaCC.SelectedValue.ToString() + "'";
            }
            DataTable dtu = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql_u).Tables[0];
            string sophieu = "";
            if (dtu.Rows.Count > 0)
            {
                for (int i = 0; i < dtu.Rows.Count; i++)
                {
                    sophieu += dtu.Rows[i]["sophieu"].ToString() + "','";
                }
                sophieu = sophieu.Substring(0, sophieu.Length - 2);
                sophieu = " and sophieu in ('" + sophieu + ")";
            }
            else
            {
                sophieu = " and sophieu = '0'";
            }
            return sophieu;
        }

        /// <summary>
        /// Xem dữ liệu
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        public override void btnView_Click(object sender, EventArgs e)
        {
            base.btnView_Click(sender, e);
            getData(getDetailsName());
            
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        
        private void frmTK_NhapHangcs_Load(object sender, EventArgs e)
        {
            this.dgvdata.AutoGenerateColumns = false;
            this.dgvdatact.AutoGenerateColumns = false;
            loadLoaiThuoc();
            loadNCC();
            loadLoaiPhieu();
        }
        private void loadLoaiThuoc()
        {
            string sql = "select MANHOM,TENNHOM from zD_DM_VATTU_NHOMVATTU union select '',N'--Tất cả--'";
            DataTable dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                this.cboTenNhom.DataSource = dt;
                this.cboTenNhom.ValueMember = "MANHOM";
                this.cboTenNhom.DisplayMember = "TENNHOM";
                this.cboTenNhom.SelectedValue = "";
            }
        }
        private void loadThuoc(string where)
        {
            string sql = "select MAVT,TENVT from zD_DM_THUOC " + where + " union select '',N'--Tất cả--' ";
            DataTable dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                this.cboTenThuoc.DataSource = dt;
                this.cboTenThuoc.ValueMember = "MAVT";
                this.cboTenThuoc.DisplayMember = "TENVT";
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void dgvdata_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            viewDetails();
        }

        private void viewDetails()
        {
            try
            {
                string sql = "SELECT * FROM view_THUOC_PHIEUNHAPKHOCT where sophieu='" + dgvdata["sophieu", dgvdata.CurrentRow.Index].Value.ToString() + "'";
                DataTable dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql).Tables[0];
                if (dt.Rows.Count > 0)
                {
                    this.dgvdatact.DataSource = dt;
                }
            }
            catch { }
        }

        /// <summary>
        /// 
        /// </summary>
        private void loadLoaiPhieu()
        {
            string sql = "SELECT Id,Name FROM zD_LOAIPHIEU UNION select '',N'--Tất cả--'";
            DataTable dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                cboLoaiPhieu.DataSource = dt;
                cboLoaiPhieu.ValueMember = "Id";
                cboLoaiPhieu.DisplayMember = "Name";
            }
            cboLoaiPhieu.SelectedIndex = 0;
        }

        private void dgvdata_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            string HDType = "";
            if (cboLoaiPhieu.SelectedValue.ToString() == "1")
            {
                HDType = "HDNH";
            }
            else if (cboLoaiPhieu.SelectedValue.ToString() == "2")
            {
                HDType ="HDKK";
            }
            frmHD_NhapHang frm = new frmHD_NhapHang("Nguyễn Thế Sơn", "Công nghệ thông tin", HDType, "edit", dgvdata["sophieu", dgvdata.CurrentRow.Index].Value.ToString());
            frm.ShowDialog();
        }

        private void dgvdata_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void mnuSuaPhieu_Click(object sender, EventArgs e)
        {
            viewDetails();
        }
        public override void btnExcel_Click(object sender, EventArgs e)
        {
            System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            //this.prgXuatExcel.Visible = true;
            base.btnExcel_Click(sender, e);

            double progress = 0;
            //this.prgXuatExcel.Maximum = dgvData.Rows.Count;

            COMExcel.Application exApp = new COMExcel.Application();
            // Thêm file temp xls
            COMExcel.Workbook exBook = exApp.Workbooks.Add(
                      COMExcel.XlWBATemplate.xlWBATWorksheet);
            // Lấy sheet 1.
            COMExcel.Worksheet exSheet = (COMExcel.Worksheet)exBook.Worksheets[1];
            exSheet.Name = "KIEM KE";

            COMExcel.Range RE_TIEUDE = (COMExcel.Range)exSheet.get_Range("A1", "A1");
            RE_TIEUDE.Value2 = "Bệnh viện MEDLATEC";
            RE_TIEUDE.EntireRow.Font.Name = "Arial";
            RE_TIEUDE.EntireRow.Font.Size = 14;
            RE_TIEUDE.EntireRow.Font.Bold = true;
            exSheet.get_Range("A1", "N1").Merge(false);
            exSheet.get_Range("A1", "A1").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            COMExcel.Range RE_BC = (COMExcel.Range)exSheet.get_Range("A2", "A2");
            RE_BC.Value2 = "BẢNG TỔNG HỢP NHẬP THUỐC NGÀY 10 THÁNG 10 NĂM 2012";
            RE_BC.EntireRow.Font.Name = "Arial";
            RE_BC.EntireRow.Font.Size = 14;
            RE_BC.EntireRow.Font.Bold = true;
            exSheet.get_Range("A2", "N2").Merge(false);
            exSheet.get_Range("A2", "A2").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            COMExcel.Range HEAD_SOTIENHAP = (COMExcel.Range)exSheet.get_Range("D3", "D3");
            HEAD_SOTIENHAP.Value2 = "Số tiền nhập :";
            HEAD_SOTIENHAP.EntireRow.Font.Name = "Arial";
            HEAD_SOTIENHAP.EntireRow.Font.Size = 10;
            HEAD_SOTIENHAP.EntireRow.Font.Bold = true;
            exSheet.get_Range("D3", "D3").Cells.HorizontalAlignment = COMExcel.Constants.xlRight;

            COMExcel.Range HEAD_SOTIENDUKIENBANRA = (COMExcel.Range)exSheet.get_Range("D4", "D4");
            HEAD_SOTIENDUKIENBANRA.Value2 = "Số tiền dự kiến bán ra :";
            HEAD_SOTIENDUKIENBANRA.EntireRow.Font.Name = "Arial";
            HEAD_SOTIENDUKIENBANRA.EntireRow.Font.Size = 10;
            HEAD_SOTIENDUKIENBANRA.EntireRow.Font.Bold = true;
            exSheet.get_Range("D4", "D4").Cells.HorizontalAlignment = COMExcel.Constants.xlRight;

            COMExcel.Range HEAD_NGUOINHAPHANG = (COMExcel.Range)exSheet.get_Range("K3", "K3");
            HEAD_NGUOINHAPHANG.Value2 = "Người nhập hàng :";
            HEAD_NGUOINHAPHANG.EntireRow.Font.Name = "Arial";
            HEAD_NGUOINHAPHANG.EntireRow.Font.Size = 10;
            HEAD_NGUOINHAPHANG.EntireRow.Font.Bold = true;
            exSheet.get_Range("K3", "K3").Cells.HorizontalAlignment = COMExcel.Constants.xlRight;


            COMExcel.Range HEAD_TYLELAIDUTINH = (COMExcel.Range)exSheet.get_Range("K4", "K4");
            HEAD_TYLELAIDUTINH.Value2 = "Tỷ lệ lãi dự tính :";
            HEAD_TYLELAIDUTINH.EntireRow.Font.Name = "Arial";
            HEAD_TYLELAIDUTINH.EntireRow.Font.Size = 10;
            HEAD_TYLELAIDUTINH.EntireRow.Font.Bold = true;
            exSheet.get_Range("K4", "K4").Cells.HorizontalAlignment = COMExcel.Constants.xlRight;


            #region HEADER DULIEU
            //STT
            COMExcel.Range HEAD_STT = (COMExcel.Range)exSheet.get_Range("A5", "A5");
            HEAD_STT.Value2 = "STT";
            HEAD_STT.EntireRow.Font.Name = "Arial";
            HEAD_STT.EntireRow.Font.Size = 10;
            HEAD_STT.EntireRow.Font.Bold = true;
            exSheet.get_Range("A5", "A6").Merge(false);
            exSheet.get_Range("A5", "A5").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            //MA
            COMExcel.Range HEAD_MA = (COMExcel.Range)exSheet.get_Range("B5", "B5");
            HEAD_MA.Value2 = "Mã";
            HEAD_MA.EntireRow.Font.Name = "Arial";
            HEAD_MA.EntireRow.Font.Size = 10;
            HEAD_MA.EntireRow.Font.Bold = true;
            exSheet.get_Range("B5", "B6").Merge(false);
            exSheet.get_Range("B5", "B5").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            //NGAYMUA
            COMExcel.Range HEAD_NGAYMUA = (COMExcel.Range)exSheet.get_Range("C5", "C5");
            HEAD_NGAYMUA.Value2 = "Ngày mua";
            HEAD_NGAYMUA.EntireRow.Font.Name = "Arial";
            HEAD_NGAYMUA.EntireRow.Font.Size = 10;
            HEAD_NGAYMUA.EntireRow.Font.Bold = true;
            exSheet.get_Range("C5", "C6").Merge(false);
            exSheet.get_Range("C5", "C5").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            //TENTHUOC
            COMExcel.Range HEAD_TEN = (COMExcel.Range)exSheet.get_Range("D5", "D5");
            HEAD_TEN.Value2 = "Tên thuốc";
            HEAD_TEN.EntireRow.Font.Name = "Arial";
            HEAD_TEN.EntireRow.Font.Size = 10;
            HEAD_TEN.EntireRow.Font.Bold = true;
            exSheet.get_Range("D5", "D6").Merge(false);
            exSheet.get_Range("D5", "D5").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            //NGAY
            COMExcel.Range HEAD_NGAY = (COMExcel.Range)exSheet.get_Range("E5", "E5");
            HEAD_NGAY.Value2 = "Ngày";
            HEAD_NGAY.EntireRow.Font.Name = "Arial";
            HEAD_NGAY.EntireRow.Font.Size = 10;
            HEAD_NGAY.EntireRow.Font.Bold = true;
            exSheet.get_Range("E5", "F5").Merge(false);
            exSheet.get_Range("E5", "E5").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            //SOLOXN
            COMExcel.Range HEAD_SOLO = (COMExcel.Range)exSheet.get_Range("E6", "E6");
            HEAD_SOLO.Value2 = "Số lô SX";
            HEAD_SOLO.EntireRow.Font.Name = "Arial";
            HEAD_SOLO.EntireRow.Font.Size = 10;
            HEAD_SOLO.EntireRow.Font.Bold = true;
            exSheet.get_Range("E6", "E6").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            //HET HAN
            COMExcel.Range HEAD_HETHAN = (COMExcel.Range)exSheet.get_Range("F6", "F6");
            HEAD_HETHAN.Value2 = "Hết hạn";
            HEAD_HETHAN.EntireRow.Font.Name = "Arial";
            HEAD_HETHAN.EntireRow.Font.Size = 10;
            HEAD_HETHAN.EntireRow.Font.Bold = true;
            exSheet.get_Range("F6", "F6").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            //ĐVT
            COMExcel.Range HEAD_DVT = (COMExcel.Range)exSheet.get_Range("G5", "G5");
            HEAD_DVT.Value2 = "ĐVT";
            HEAD_DVT.EntireRow.Font.Name = "Arial";
            HEAD_DVT.EntireRow.Font.Size = 10;
            HEAD_DVT.EntireRow.Font.Bold = true;
            exSheet.get_Range("G5", "G6").Merge(false);
            exSheet.get_Range("G5", "G5").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            //Nhập
            COMExcel.Range HEAD_NHAPVAO = (COMExcel.Range)exSheet.get_Range("H5", "H5");
            HEAD_NHAPVAO.Value2 = "Nhập vào";
            HEAD_NHAPVAO.EntireRow.Font.Name = "Arial";
            HEAD_NHAPVAO.EntireRow.Font.Size = 10;
            HEAD_NHAPVAO.EntireRow.Font.Bold = true;
            exSheet.get_Range("H5", "J5").Merge(false);
            exSheet.get_Range("H5", "H5").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            //TONGNHAP
            COMExcel.Range HEAD_TONGNHAP = (COMExcel.Range)exSheet.get_Range("H6", "H6");
            HEAD_TONGNHAP.Value2 = "Tổng nhập";
            HEAD_TONGNHAP.EntireRow.Font.Name = "Arial";
            HEAD_TONGNHAP.EntireRow.Font.Size = 10;
            HEAD_TONGNHAP.EntireRow.Font.Bold = true;
            exSheet.get_Range("H6", "H6").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            //GIA NHAP
            COMExcel.Range HEAD_GIA = (COMExcel.Range)exSheet.get_Range("I6", "I6");
            HEAD_GIA.Value2 = "Giá nhập";
            HEAD_GIA.EntireRow.Font.Name = "Arial";
            HEAD_GIA.EntireRow.Font.Size = 10;
            HEAD_GIA.EntireRow.Font.Bold = true;
            exSheet.get_Range("I6", "I6").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            //THANH TIEN NHAP
            COMExcel.Range HEAD_THANHTIENNHAP = (COMExcel.Range)exSheet.get_Range("J6", "J6");
            HEAD_THANHTIENNHAP.Value2 = "Thành tiền";
            HEAD_THANHTIENNHAP.EntireRow.Font.Name = "Arial";
            HEAD_THANHTIENNHAP.EntireRow.Font.Size = 10;
            HEAD_THANHTIENNHAP.EntireRow.Font.Bold = true;
            exSheet.get_Range("J6", "J6").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            //Xuất
            COMExcel.Range HEAD_XUATRA = (COMExcel.Range)exSheet.get_Range("K5", "K5");
            HEAD_XUATRA.Value2 = "Xuất ra";
            HEAD_XUATRA.EntireRow.Font.Name = "Arial";
            HEAD_XUATRA.EntireRow.Font.Size = 10;
            HEAD_XUATRA.EntireRow.Font.Bold = true;
            exSheet.get_Range("K5", "M5").Merge(false);
            exSheet.get_Range("K5", "K5").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            //TONGXUAT
            COMExcel.Range HEAD_TONGXUAT = (COMExcel.Range)exSheet.get_Range("K6", "K6");
            HEAD_TONGXUAT.Value2 = "Tổng xuất";
            HEAD_TONGXUAT.EntireRow.Font.Name = "Arial";
            HEAD_TONGXUAT.EntireRow.Font.Size = 10;
            HEAD_TONGXUAT.EntireRow.Font.Bold = true;
            exSheet.get_Range("K6", "K6").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            //GIA XUAT
            COMExcel.Range HEAD_GIAXUAT = (COMExcel.Range)exSheet.get_Range("L6", "L6");
            HEAD_GIAXUAT.Value2 = "Giá xuất";
            HEAD_GIAXUAT.EntireRow.Font.Name = "Arial";
            HEAD_GIAXUAT.EntireRow.Font.Size = 10;
            HEAD_GIAXUAT.EntireRow.Font.Bold = true;
            exSheet.get_Range("L6", "L6").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            //THANH TIEN NHAP
            COMExcel.Range HEAD_THANHTIENXUAT = (COMExcel.Range)exSheet.get_Range("M6", "M6");
            HEAD_THANHTIENXUAT.Value2 = "Thành tiền";
            HEAD_THANHTIENXUAT.EntireRow.Font.Name = "Arial";
            HEAD_THANHTIENXUAT.EntireRow.Font.Size = 10;
            HEAD_THANHTIENXUAT.EntireRow.Font.Bold = true;
            exSheet.get_Range("M6", "M6").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            //NGUOI NHAP
            COMExcel.Range HEAD_NGUOINHAP = (COMExcel.Range)exSheet.get_Range("N5", "N5");
            HEAD_NGUOINHAP.Value2 = "Người nhập";
            HEAD_NGUOINHAP.EntireRow.Font.Name = "Arial";
            HEAD_NGUOINHAP.EntireRow.Font.Size = 10;
            HEAD_NGUOINHAP.EntireRow.Font.Bold = true;
            exSheet.get_Range("N5", "N6").Merge(false);
            exSheet.get_Range("N5", "N5").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            //Tỷ lệ lãi xuất
            COMExcel.Range HEAD_TYLELAIXUAT = (COMExcel.Range)exSheet.get_Range("O5", "O5");
            HEAD_TYLELAIXUAT.Value2 = "Tỷ lệ lãi xuất";
            HEAD_TYLELAIXUAT.EntireRow.Font.Name = "Arial";
            HEAD_TYLELAIXUAT.EntireRow.Font.Size = 10;
            HEAD_TYLELAIXUAT.EntireRow.Font.Bold = true;
            exSheet.get_Range("O5", "O6").Merge(false);
            exSheet.get_Range("O5", "O5").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;
            DataTable dt = new DataTable();
            dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, "select * from zD_bcTonkhoth").Tables[0];
            //this.prgXuatExcel.Maximum = dt.Rows.Count;
            exSheet.get_Range("A5", "O6").Borders.ColorIndex = 1;
            exSheet.get_Range("A5", "O6").Interior.ColorIndex = 6;

            exSheet.get_Range("C5", "C5").Application.ActiveWindow.FreezePanes = true;
            int start = 7;
            #endregion

            #region DU LIEU
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    progress = i + 1;
                    // hien thi chay % tren thanh process, lam tron 2 ki tu
                    //lblText.Text = Math.Round(((progress / dt.Rows.Count) * 100), 2).ToString() + "%";
                    //prgXuatExcel.Value = i + 1;
                    Thread.Sleep(2);
                    Application.DoEvents();
                    //STT
                    COMExcel.Range V_STT = (COMExcel.Range)exSheet.get_Range("A" + (i + start), "A" + (i + start));
                    V_STT.Value2 = (i + 1).ToString();
                    V_STT.EntireColumn.AutoFit();

                    //MAVT
                    COMExcel.Range V_MAVT = (COMExcel.Range)exSheet.get_Range("B" + (i + start), "B" + (i + start));
                    V_MAVT.Value2 = dt.Rows[i]["mavt"].ToString();
                    V_MAVT.EntireColumn.AutoFit();

                    //TENVT
                    COMExcel.Range V_TENVT = (COMExcel.Range)exSheet.get_Range("D" + (i + start), "D" + (i + start));
                    V_TENVT.Value2 = dt.Rows[i]["tenvt"].ToString();
                    V_TENVT.EntireColumn.AutoFit();

                    //HANSD
                    COMExcel.Range V_HANSD = (COMExcel.Range)exSheet.get_Range("F" + (i + start), "F" + (i + start));
                    V_HANSD.Value2 = dt.Rows[i]["hansd"].ToString();
                    V_HANSD.EntireColumn.AutoFit();

                    //DVT
                    COMExcel.Range V_DVT = (COMExcel.Range)exSheet.get_Range("G" + (i + start), "G" + (i + start));
                    V_DVT.Value2 = dt.Rows[i]["dvt"].ToString();
                    V_DVT.EntireColumn.AutoFit();


                    //SLNHAP
                    COMExcel.Range V_SLNHAP = (COMExcel.Range)exSheet.get_Range("H" + (i + start), "H" + (i + start));
                    V_SLNHAP.Value2 = dt.Rows[i]["slnhap"].ToString();
                    V_SLNHAP.EntireColumn.AutoFit();

                    //TIENNHAP
                    COMExcel.Range V_TIENNHAP = (COMExcel.Range)exSheet.get_Range("J" + (i + start), "J" + (i + start));
                    V_TIENNHAP.Value2 = dt.Rows[i]["tiennhap"].ToString();
                    V_TIENNHAP.EntireColumn.AutoFit();

                    //SLXUAT
                    COMExcel.Range V_SLXUAT = (COMExcel.Range)exSheet.get_Range("K" + (i + start), "K" + (i + start));
                    V_SLXUAT.Value2 = dt.Rows[i]["slxuat"].ToString();
                    V_SLXUAT.EntireColumn.AutoFit();

                    //TIENXUAT
                    COMExcel.Range V_TIENXUAT = (COMExcel.Range)exSheet.get_Range("K" + (i + start), "K" + (i + start));
                    V_TIENXUAT.Value2 = dt.Rows[i]["tienxuat"].ToString();
                    V_TIENXUAT.EntireColumn.AutoFit();

                    //TYLELAIXUAT
                    COMExcel.Range V_TYLELAIXUAT = (COMExcel.Range)exSheet.get_Range("O" + (i + start), "O" + (i + start));
                    V_TYLELAIXUAT.Value2 = "0";
                    V_TYLELAIXUAT.EntireColumn.AutoFit();
                }

            }
            int countColums = dt.Rows.Count + start;
            exSheet.get_Range("A7", "O" + countColums).Borders.ColorIndex = 1;

            #endregion

            COMExcel.Range V_TONGTIENNHAP = (COMExcel.Range)exSheet.get_Range("E3", "E3");
            
            V_TONGTIENNHAP.Value2 = "=SUM(J" + start + ":J" + countColums + ")";
            V_TONGTIENNHAP.EntireRow.Font.Name = "Arial";
            V_TONGTIENNHAP.EntireRow.Font.Size = 10;
            V_TONGTIENNHAP.EntireRow.Font.Bold = true;
            exSheet.get_Range("E3", "E3").Cells.HorizontalAlignment = COMExcel.Constants.xlLeft;

            #region SAVE FILE
            // Ẩn chương trình
            exApp.Visible = false;

            // Save file
            exBook.SaveAs("D:\\BC_NHAPHANG_DUOC.xls", COMExcel.XlFileFormat.xlWorkbookNormal,
                            null, null, false, false,
                            COMExcel.XlSaveAsAccessMode.xlExclusive,
                            false, false, false, false, false);
            exApp.Quit();
            MessageBox.Show("Lưu thành công tại : D:\\BC_NHAPHANG_DUOC.xls", "Thông báo");

            FileInfo fi = new FileInfo("D:\\BC_NHAPHANG_DUOC.xls");
            if (fi.Exists)
            {
                System.Diagnostics.Process.Start(@"D:\\BC_NHAPHANG_DUOC.xls");
            }
            else
            {
                //
            }
            #endregion
           // this.prgXuatExcel.Visible = false;
        }

        private void cboTenNhom_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.loadThuoc("");
        }
        public override void btnExportReport_Click(object sender, EventArgs e)
        {
            base.btnExportReport_Click(sender, e);

            /*
                @Tungay  datetime,
                @Denngay datetime,
                @mavt nvarchar(30),
                @manhom  nvarchar(30),
                @nhacc nvarchar(30),
                @wherein NVARCHAR(300)
             */
            SqlParameter[] spParameter = new SqlParameter[6];

            spParameter[0] = new SqlParameter("@Tungay", SqlDbType.DateTime, 8);
            spParameter[0].Direction = ParameterDirection.Input;
            spParameter[0].Value = dtpFrom.Value;

            spParameter[1] = new SqlParameter("@Denngay", SqlDbType.DateTime, 8);
            spParameter[1].Direction = ParameterDirection.Input;
            spParameter[1].Value = dtpTo.Value;

            spParameter[2] = new SqlParameter("@mavt", SqlDbType.NVarChar, 30);
            spParameter[2].Direction = ParameterDirection.Input;
            spParameter[2].Value = "";

            spParameter[3] = new SqlParameter("@manhom", SqlDbType.NVarChar, 30);
            spParameter[3].Direction = ParameterDirection.Input;
            spParameter[3].Value = "";

            spParameter[4] = new SqlParameter("@nhacc", SqlDbType.NVarChar, 30);
            spParameter[4].Direction = ParameterDirection.Input;
            spParameter[4].Value = "";

            spParameter[5] = new SqlParameter("@wherein", SqlDbType.NVarChar, 300);
            spParameter[5].Direction = ParameterDirection.Input;
            spParameter[5].Value = "";

            DataTable dt = new DataTable();
            dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"],
                CommandType.StoredProcedure,
                "zD_BC_TONKHO",
                spParameter).Tables[0];
            frmShowReport frm = new frmShowReport(1);
            frmShowReport._dt = dt;
            frmShowReport.strC = frmShowReport.BC.TKNHAPHANG;
            frmShowReport._tungay = dtpFrom.Value.ToString("dd/MM/yyyy");
            frmShowReport._denngay = dtpTo.Value.ToString("dd/MM/yyyy");
            float tongtiennhap = 0;
            float tongtienban = 0;
            float tonglai = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                tongtiennhap += float.Parse(dt.Rows[i]["thanhtien"].ToString());
                tongtienban += float.Parse(dt.Rows[i]["tongban"].ToString());
                tonglai += float.Parse(dt.Rows[i]["lai"].ToString());
            }
            frmShowReport._thanhtien = String.Format("{0:0,0}", tongtiennhap);
            frmShowReport._tonglai = String.Format("{0:0,0}", tonglai);
            frmShowReport._tongban = String.Format("{0:0,0}", tongtienban);
            frm.Show();
        }

        private void dgvdatact_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            this.txtMaThuoc.Text = dgvdatact["_sophieu", dgvdatact.CurrentRow.Index].Value.ToString();
            this.txtTenThuoc.Text = dgvdatact["tenvt", dgvdatact.CurrentRow.Index].Value.ToString();
            this.txtSoluong.Text = dgvdatact["soluong", dgvdatact.CurrentRow.Index].Value.ToString();
            this.txtThanhTien.Text = String.Format("{0:0,0}",double.Parse(dgvdatact["thanhtien", dgvdatact.CurrentRow.Index].Value.ToString()));
            this.txtSoloCt.Text = dgvdatact["solo", dgvdatact.CurrentRow.Index].Value.ToString();
            this.txtNgaysx.Text = dgvdatact["ngaysx", dgvdatact.CurrentRow.Index].Value.ToString();
            this.txtMaVach.Text = dgvdatact["mavach", dgvdatact.CurrentRow.Index].Value.ToString();
            this.txtDVT.Text = dgvdatact["ten", dgvdatact.CurrentRow.Index].Value.ToString();
            this.txtDongGiaNhap.Text = String.Format("{0:0,0}",double.Parse(dgvdatact["dongianhap", dgvdatact.CurrentRow.Index].Value.ToString()));
            this.txtDonGiaBan.Text = String.Format("{0:0,0}",double.Parse(dgvdatact["giaban", dgvdatact.CurrentRow.Index].Value.ToString()));
        }

        private void keyup(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                btnView_Click(sender, e);
        }

        private void cboNhaCC_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
    }
}
